using System;
using System.Data;
using System.Collections;
using CE.Database;
/// <summary>
/// Summary description for CarDetailDB
/// </summary>
/// 
namespace CE.CarInsurance
{
    public class CarDetailDB
    {
        public CarDetailDB()
        {
            //
            // TODO: Add constructor logic here
            //
        }

        public Int64 Insert(CarDetail cardetail)
        {
            MyDatabase _db = new MyDatabase();
            Int64 _result = -1;
            string _spName = "sprocCarDetailInsertUpdateSingleItem";
            
            SortedList _sPara = new SortedList();
            
            #region Parameter
                _sPara.Add("@CECarId", cardetail.CECarId);
                _sPara.Add("@CarMakeId", cardetail.CarMakeId);
                _sPara.Add("@CarModelId", cardetail.CarModelId);
                _sPara.Add("@ManufactYear", cardetail.ManufactYear);
                _sPara.Add("@RegYear", cardetail.RegYear);

                _sPara.Add("@MakeModelDesc", cardetail.MakeModelDesc);
                _sPara.Add("@IsPeak", cardetail.IsPeak);
                _sPara.Add("@IsParalleImport", cardetail.IsParalleImport);
                _sPara.Add("@SeatCapacity", cardetail.SeatCapacity);
                _sPara.Add("@RefCEUserId", cardetail.RefCEUserId);
                _sPara.Add("@CurrentStep", cardetail.CurrentStep);
                _sPara.Add("@TotalStep", cardetail.TotalStep);
                _sPara.Add("@ViewedDetailId", cardetail.ViewedDetailId);
                _sPara.Add("@Status", cardetail.Status);
                _sPara.Add("@Info1", cardetail.Info1);
                _sPara.Add("@Info2", cardetail.Info2);
                _sPara.Add("@Info3", cardetail.Info3);
                _sPara.Add("@Info4", cardetail.Info4);
                _sPara.Add("@Info5", cardetail.Info5);
                _sPara.Add("@Info6", cardetail.Info6);
                _sPara.Add("@Info7", cardetail.Info7);
                _sPara.Add("@Info8", cardetail.Info8);
                _sPara.Add("@Info9", cardetail.Info9);
                _sPara.Add("@Info10", cardetail.Info10);
                _sPara.Add("@Info11", cardetail.Info11);
                _sPara.Add("@Info12", cardetail.Info12);
                _sPara.Add("@Info13", cardetail.Info13);
                _sPara.Add("@Info14", cardetail.Info14);
                _sPara.Add("@Info15", cardetail.Info15);
                _sPara.Add("@Info16", cardetail.Info16);
                _sPara.Add("@Info17", cardetail.Info17);
                _sPara.Add("@Info18", cardetail.Info18);
                _sPara.Add("@Info19", cardetail.Info19);
                _sPara.Add("@Info20", cardetail.Info20);
            #endregion

            _result = _db.ExecuteSP(_spName, _sPara);

            return _result;
        }

        public Int64 GetCarModelCount(int CarMakeId)
        {
            MyDatabase _db = new MyDatabase();
            object _result = null;
            Int64 _retValue = 0;

            _result = _db.ExecuteScalarSqlStatement("Select count(CarMakeId) from tblCarModelInfo Where [Status] = 1 And [CarMakeId] = " + CarMakeId.ToString());
            if (_result != null)
            {
                _retValue = Convert.ToInt64(_result);
            }
            return _retValue;
        }


        public Int64 UpdateRegisterYear(CarDetail cardetail)
        {
            MyDatabase _db = new MyDatabase();
            Int64 _result = -1;
            string _spName = "sprocCarDetailUpdateRegisterYearSingleItem";

            SortedList _sPara = new SortedList();

            #region Parameter
            _sPara.Add("@CECarId", cardetail.CECarId);
            _sPara.Add("@RegYear", cardetail.RegYear);
            _sPara.Add("@RefCEUserId", cardetail.RefCEUserId);
            #endregion

            _result = _db.ExecuteSP(_spName, _sPara);

            return _result;
        }

        
        public DataTable GetCarModel()
        {
            MyDatabase _db = new MyDatabase();
            string strSQL = "SELECT    MA.CarMakeId, MD.CarModelId, MD.CarCode, MA.Description AS CarMake, MD.Description AS Model, MD.Body, MD.CC " +
                            "FROM         tblCarMakeInfo AS MA INNER JOIN "+
                            "tblCarModelInfo AS MD ON MA.CarMakeId = MD.CarMakeId WHERE MD.[Status] = 1 ORDER BY MA.Description, MD.[Description]";

            DataTable _dtCarModel = _db.GetDataTable(strSQL);

            return _dtCarModel;
        }

        public DataTable GetCarModel(int CarMakeId)
        {
            MyDatabase _db = new MyDatabase();
            string strSQL = "SELECT    MA.CarMakeId, MD.CarModelId, MD.CarCode, MA.Description AS CarMake, MD.Description AS Model, MD.Body, MD.CC " +
                            "FROM         tblCarMakeInfo AS MA INNER JOIN " +
                            "tblCarModelInfo AS MD ON MA.CarMakeId = MD.CarMakeId WHERE MD.[Status] = 1 And MA.CarMakeId = " + CarMakeId + " ORDER BY MA.Description, MD.[Description]";

            DataTable _dtCarModel = _db.GetDataTable(strSQL);

            return _dtCarModel;
        }

        public DataTable GetAllCarModel()
        {
            MyDatabase _db = new MyDatabase();
            string strSQL = @"Select MD.CarMakeId, MD.CarModelId, MD.[Description], MD.Body, MD.CC from tblCarModelInfo MD
                            INNER JOIN tblCarMakeInfo MA ON MA.CarMakeId = MD.CarMakeId 
                            Where MA.Status = 1 AND MD.Status = 1  ORDER BY MA.Description, MD.[Description]";

            DataTable _dtCarModel = _db.GetDataTable(strSQL);

            return _dtCarModel;
        }

        public bool Update()
        {
            return true;
        }

        public bool Delete()
        {
            return true;
        }

        public CarDetail GetCar(Int64 CarId, Int64 CEUserId)
        {
            CarDetail _cardetail = null;
            MyDatabase _db = new MyDatabase();
            string _spName = "sprocCarDetailSelectSingleItem";

            SortedList _spara = new SortedList();
            _spara.Add("@CECarID", CarId);
            _spara.Add("@RefCEUserId", CEUserId);

            using (DataTable _dt = _db.GetDataTableBySP(_spName,_spara))
            {
                if (_dt != null && _dt.Rows.Count > 0)
                {
                    _cardetail = new CarDetail();
                    _cardetail = FillDataRecord(_dt);
                }
            }
            return _cardetail;
        }

        public CarDetail GetCar(string GUID)
        {
            CarDetail _cardetail = null;
            MyDatabase _db = new MyDatabase();
            string _spName = "sprocCarDetailByGUIDSelectSingleItem";

            SortedList _spara = new SortedList();
            _spara.Add("@GUID", GUID);

            using (DataTable _dt = _db.GetDataTableBySP(_spName, _spara))
            {
                if (_dt != null && _dt.Rows.Count > 0)
                {
                    _cardetail = new CarDetail();
                    _cardetail = FillDataRecord(_dt);
                }
            }
            return _cardetail;
        }

        public string GetGUID(Int64 CarId)
        {
            MyDatabase _db = new MyDatabase();
            string strSQL = "Select ROWGUID from tblCarDetail Where [CECarId] = @CECarID";

            SortedList _spara = new SortedList();
            _spara.Add("@CECarID", CarId);

            object _result = _db.ExecuteScalarSqlStatement(strSQL, _spara);

            return _result.ToString();
        }

        private CarDetail FillDataRecord(DataTable myDataTable)
        {
            CarDetail myCar = new CarDetail();
            myCar.CECarId = Convert.ToInt64(myDataTable.Rows[0]["CECarID"].ToString());
            myCar.CarMakeId = Convert.ToInt32(myDataTable.Rows[0]["CarMakeId"].ToString());
            myCar.CarModelId = Convert.ToInt32(myDataTable.Rows[0]["CarModelId"].ToString());
            myCar.MakeModelDesc = myDataTable.Rows[0]["MakeModelDesc"].ToString();
            myCar.ManufactYear = Convert.ToInt32(myDataTable.Rows[0]["ManufactYear"].ToString());
            myCar.RegYear = Convert.ToInt32(myDataTable.Rows[0]["RegYear"].ToString());
            
            myCar.IsPeak = Convert.ToBoolean(myDataTable.Rows[0]["IsPeak"].ToString());
            myCar.IsParalleImport = Convert.ToBoolean(myDataTable.Rows[0]["IsParalleImport"].ToString());
            myCar.SeatCapacity = myDataTable.Rows[0]["SeatCapacity"].ToString();
            myCar.RefCEUserId = Convert.ToInt64(myDataTable.Rows[0]["RefCEUserId"].ToString());
            myCar.Status = Convert.ToBoolean(myDataTable.Rows[0]["Status"].ToString());
            myCar.Info1 = myDataTable.Rows[0]["Info1"].ToString();
            myCar.Info5 = myDataTable.Rows[0]["Info5"].ToString();
            myCar.Info6 = myDataTable.Rows[0]["Info6"].ToString();
            myCar.Info7 = myDataTable.Rows[0]["Info7"].ToString();
            myCar.Info8 = myDataTable.Rows[0]["Info8"].ToString();
            myCar.Info9 = myDataTable.Rows[0]["Info9"].ToString();
            myCar.Info10 = myDataTable.Rows[0]["Info10"].ToString();
            myCar.Info11 = myDataTable.Rows[0]["Info11"].ToString();
            myCar.Info12 = myDataTable.Rows[0]["Info12"].ToString();
            myCar.Info13 = myDataTable.Rows[0]["Info13"].ToString();
            myCar.Info14 = myDataTable.Rows[0]["Info14"].ToString();
            return myCar;
        }
    }
}